# coding=utf-8

import os
from flask import Flask
from flask import request,jsonify,render_template,redirect,url_for
from datetime import timedelta
from werkzeug.utils import secure_filename
from pycacho.cachobase.base_config import db,cursor

from homework.src.make_invoices import mk_invoices
from homework.src.count_nums import get_item

app = Flask(__name__)    # 初始化一个Flask对象作为服务器


#默认路径访问登录页面
@app.route('/')
def login():
    return render_template('login.html')

@app.route('/login')
def login_request():
    # SQL 查询语句
    sql = 'select * from home_user where user_name="%s" and password="%s"'\
          %(request.args.get('user_name'),request.args.get('password'))
    print(sql)
    try:
        # 执行sql语句
        cursor.execute(sql)
        results = cursor.fetchall()
        #print(len(results))
        if len(results) == 1:
            print('登录成功')
            return render_template('index.html')
        else:
            print('用户名或密码不正确')
            return render_template('login.html')
    except:
        # 如果发生错误则回滚
        db.rollback()
    db.close()

@app.route('/upload', methods=['GET'])
def upload():
    return render_template('upload.html')

@app.route('/balance', methods=['GET'])
def balance():
    return render_template('balance.html')

@app.route('/count', methods=['GET'])
def count():
    return render_template('count.html')


# 设置允许的文件格式
ALLOWED_EXTENSIONS = ['png', 'jpg', 'JPG', 'PNG', 'xls','xlsx']
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS

# 设置静态文件缓存过期时间
app.send_file_max_age_default = timedelta(seconds=1)

@app.route('/upload',methods=['POST', 'GET'])
def upload_file():
    if request.method == 'POST':
        # 通过file标签获取文件
        f = request.files['file']
        if not (f and allowed_file(f.filename)):
            return jsonify({"error": 1001, "msg": "图片类型：png、PNG、jpg、JPG、bmp"})
        # 当前文件所在路径
        basepath = os.path.dirname(__file__)
        # 一定要先创建该文件夹，不然会提示没有该路径
        # secure_filename 此函数获取文件名时安全，但对中文名获取时，获取不到中文，需要修改源码的编码
        upload_path = os.path.join(basepath, 'static\invoice', secure_filename(f.filename))
        # 保存文件
        f.save(upload_path)
        try:
            invoice_cell = int(request.form['invoice_cell'])-1
            name_cell = int(request.form['name_cell'])-1
            address_cell = int(request.form['address_cell'])-1
            print(upload_path, invoice_cell, name_cell, address_cell)
            mk_invoices(upload_path, invoice_cell, name_cell, address_cell)
        except:
            print('输入的不是数字类型！！！')

        # 返回上传成功界面
        return render_template('upload_ok.html')
        # 重新返回上传界面
    return render_template('upload.html')

@app.route('/count',methods=['POST', 'GET'])
def count_file():
    if request.method == 'POST':
        f = request.files['file']
        if not (f and allowed_file(f.filename)):
            return jsonify({"error": 1001, "msg": "Excel类型"})
        basepath = os.path.dirname(__file__)
        upload_path = os.path.join(basepath, 'static\count', secure_filename(f.filename))
        # 保存文件
        f.save(upload_path)
        try:
            st_row = int(request.form['st_row'])-1
            type_cell = int(request.form['type_cell'])-1
            print(upload_path, st_row, type_cell)
            get_item(upload_path, st_row, type_cell)
        except:
            print('输入的不是数字类型！！！')

        # 返回上传成功界面
        return render_template('count_ok.html')
        # 重新返回上传界面
    return render_template('count.html')

@app.route('/balance',methods=['POST', 'GET'])
def make_bill():
    if request.method == 'POST':
        f = request.files['file']
        if not (f and allowed_file(f.filename)):
            return jsonify({"error": 1001, "msg": "Excel类型"})
        basepath = os.path.dirname(__file__)
        upload_path = os.path.join(basepath, 'static\\bill', secure_filename(f.filename))
        f.save(upload_path)
        try:
            st_row = int(request.form['st_row'])-1
            type_cell = int(request.form['type_cell'])-1
            print(upload_path, st_row, type_cell)
            get_item(upload_path, st_row, type_cell)
        except:
            print('输入的不是数字类型！！！')

        # 返回上传成功界面
        return render_template('balance_ok.html')
        # 重新返回上传界面
    return render_template('balance.html')

if __name__ == '__main__':
    app.run(debug=True)